﻿using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class LinqSqlDemo :  System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            Bind();
            DblCategory.DataValueField = "Mark";
            DblCategory.DataTextField = "Name";
            DblCategory.DataSource = new TestDBDataContext().MyCategory;
            DblCategory.DataBind();
        }


    }

    

    protected void Btn_search_Click(object sender, EventArgs e)
    {
        string searchKey = TbKeyword.Text.Trim();
        Bind();



    }
    private object GetData(string searchKey)
    {
        var db = new TestDBDataContext();
        //以关键字开始
        //var results = from info in db.MyInfo
        //              where info.Title.StartsWith(searchKey)
        //              select info;
        //以关键字结束
        //var results = from info in db.MyInfo
        //              where info.Title.EndsWith(searchKey)
        //              select info;
        ////包含关健字
        //var results = from info in db.MyInfo
        //              where info.Title.Contains(searchKey)
        //              select info;
        //使用like方法
        var results = from info in db.MyInfo
                      where System.Data.Linq.SqlClient.SqlMethods.Like
                      (info.Title,"%"+searchKey+"%")
                      select info;
        Debug.WriteLine(results);
        return results;

    }
    private object GetData()
    {
        var db = new TestDBDataContext();  // 打开"Test"数据库
        // 获取info 数据表中的数据
        //var results = db.MyInfo;           
        var results = from info in db.MyInfo
                      select info;

        // where 子句
        //var results = from info in db.MyInfo
        //              where info.Click_Number >=1
        //              select info;

        // orderby 子句
        //var results = from info in db.MyInfo
        //              orderby info.Publish_Time descending  // 按降序排列
        //              select info;

        // select 指定字段显示
        //var results = from info in db.MyInfo
        //             orderby info.Publish_Time descending
        //              select new
        //              {
        //                  info.Title,
        //                  info.Content,
        //                  info.Author
        //              };
        //Debug.WriteLine(results);
        //return results;

        // select子句指定字段名
        //var results = from info in db.MyInfo
        //              orderby info.Publish_Time ascending
        //              select new
        //              {
        //                  标题 = info.Title,
        //                  正文 = info.Content,
        //                  作者 = info.Author
        //              };


        //显示关联数据
        //var results = from info in db.MyInfo
        //              orderby info.Publish_Time ascending
        //              select new
        //              {
        //                  标题 = info.Title,
        //                  正文 = info.Content,
        //                  分类 = info.MyCategory.Name
        //              };

        // 显示没有定义关系的关联数据
        //var results = from info in db.MyInfo
        //              join category in db.MyCategory on info.Category_Mark equals category.Mark
        //              orderby info.Publish_Time ascending
        //              select new
        //              {
        //                  标题 = info.Title,
        //                  正文 = info.Content,
        //                  分类 = info.MyCategory.Name
        //              };

        // linq方法
        //var results = db.MyInfo.Select(res=>res.Title);
        //var results = db.MyInfo.Sum(res=>res.Click_Number);
        //var results = db.MyInfo.Count();
        //var results = db.MyInfo.Take(3);
        //var results = db.MyInfo.Last();

        Debug.WriteLine(results);
        return results;

    }

    protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        GridView1.PageIndex = e.NewPageIndex;
        Bind(); ;

    }

    protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
    {
        GridView1.EditIndex = e.NewEditIndex;
        Bind();

    }

    protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
    {
        GridView1.EditIndex = -1;
        Bind();
    }

    protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
    {
        GridViewRow selectRow = GridView1.Rows[e.RowIndex];
        int id = Convert.ToInt32(selectRow.Cells[1].Text);
        string title = (selectRow.Cells[2].Controls[0] as TextBox).Text;
        string content = (selectRow.Cells[3].Controls[0] as TextBox).Text;
        string author = (selectRow.Cells[5].Controls[0] as TextBox).Text;
        int clickNumber = Convert.ToInt32((selectRow.Cells[6].Controls[0] as TextBox).Text);
        unodateData(id, title, content, author, clickNumber);
    }

    protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
    {
        var deleteId = Convert.ToInt32(GridView1.Rows[e.RowIndex].Cells[1].Text);
        deleteData(deleteId);
    }

    private void deleteData(int deleteId)
    {
        var db = new TestDBDataContext();
        var data = db.MyInfo.Single(res => res.Id == deleteId);
        db.MyInfo.DeleteOnSubmit(data);
        db.SubmitChanges();
    }

    protected void BtnAdd_Click(object sender, EventArgs e)
    {
        MyInfo data = new MyInfo();
        data.Title = TbTitle.Text.Trim();
        data.Content = TbContent.Text.Trim();
        data.Author = TbAuthor.Text.Trim();
        data.Publish_Time = DateTime.Now;
        data.Click_Number = 0;
        data.Category_Mark = DblCategory.SelectedValue;

        insertData(data);
        Bind();
    }

    private void insertData(MyInfo data)
    {
        var db = new TestDBDataContext();
        db.MyInfo.InsertOnSubmit(data);
        db.SubmitChanges();
    }

    private void unodateData(int id, string title, string content, string author, int clickNumber)
    {
        var db = new TestDBDataContext();
        var result = db.MyInfo.Single(res=>res.Id == id);
        result.Title = title;
        result.Content = content;
        result.Author = author;
        result.Click_Number = clickNumber;
        db.SubmitChanges();

       
    }

    private void Bind()
    {
        GridView1.DataKeyNames = new string[] { "Id" };
        GridView1.DataSource = GetData();
        GridView1.DataBind();
    }



    
}